using System;
using System.Configuration;
using System.Data;
using System.Collections;
using System.Data.OleDb;
using ManticLabs;
using ManticLabs.Common;

namespace ManticLabs.Data
{

    //TODO : use using() while declaring Command for garbige collector

    /// <summary>
    /// Summary description for DataAccess.
    /// </summary>
    /// 

    public class DataAccessOleDB //: IDataAccess
    {
        private bool mInTransaction = false;
        private OleDbConnection mConnection = null;
        private OleDbTransaction mTransaction = null;
        private OleDbDataReader mDr = null;
        private DataSet mDs = null;
        private DBMSID databaseID;
        private int queryTimeout = -1;
        public Int32 db_rtn_code = 0;
        public string db_rtn_message = "";


        public static AppModes AppMode
        {
            get
            {
                if (ConfigurationManager.AppSettings["ApplicationMode"] != null)
                {
                    switch (ConfigurationManager.AppSettings["ApplicationMode"].ToString())
                    {
                        case "Runtime":
                            return AppModes.Runtime;
                        case "Debug":
                            return AppModes.Debug;
                        default:
                            return AppModes.Default;
                    }
                }
                return AppModes.Default;
            }
        }
        

        private ArrayList fieldAttributes;

        public ArrayList getFieldAttributes()
        {
            return fieldAttributes;
        }

        public DataAccessOleDB()
        {
            mInTransaction = false;
            //this.databaseID = databaseID;
            queryTimeout =   GetFormsQueryTimeOut();
        }

        

        private string GetConnectionString()
        {
            return ConfigurationManager.ConnectionStrings["AccessDBConn"].ToString();
        }

        private void Connect()
        {
            try
            {
                mConnection = new OleDbConnection();
                // "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=c:\AccessDB\MyDB.mdb;User Id=admin;Password=;"
                mConnection.ConnectionString = GetConnectionString();
                mConnection.Open();
            }
            catch (OleDbException ex)
            {
                OleDbErrorCollection lErrors = ex.Errors;
                string userMsg;
                userMsg = "SQL SERVER: ";
                for (int i = 0; i < lErrors.Count; i++)
                    userMsg = userMsg + lErrors[i].Message + '\t';
                throw new MLabsException(userMsg, LogLevelType.SQLERROR);
            }
            catch (Exception ex)
            {
                throw new MLabsException(ex.Message, LogLevelType.ERROR);
            }
        }

        public void ExecuteSQLNonQuery(string SQLStr)
        {
            OleDbConnection connection = new OleDbConnection();
            OleDbCommand command = new OleDbCommand();

            try
            {

                if (!mInTransaction)
                {
                    connection.ConnectionString = GetConnectionString();
                    connection.Open();
                }
                else
                {
                    connection = mConnection;
                }

                command.CommandText = SQLStr;
                command.CommandTimeout = queryTimeout;

                command.CommandType = CommandType.Text;

                command.Connection = connection;

                command.ExecuteNonQuery();
                return;

            }
            catch (OleDbException ex)
            {
                HandleOleDbException(ex, SQLStr);
            }
            catch (Exception ex)
            {

                throw new MLabsException(ex.Message, LogLevelType.ERROR);
            }
            finally
            {
                try
                {
                    if (!mInTransaction)
                        connection.Close();
                }
                catch (Exception ex) { Logger.Log(ex.Source + " : " + ex.Message, LogLevelType.SQLERROR); }
            }
            return;
        }

        public object GetDBDataAsScalar(string SQLStr)
        {
            OleDbConnection connection = new OleDbConnection();
            OleDbCommand command = new OleDbCommand();

            try
            {

                if (!mInTransaction)
                {
                    connection.ConnectionString = GetConnectionString();
                    connection.Open();
                }
                else
                {
                    connection = mConnection;
                }

                command.CommandText = SQLStr;
                command.CommandTimeout = queryTimeout;

                command.CommandType = CommandType.Text;

                command.Connection = connection;

                return command.ExecuteScalar();

            }
            catch (OleDbException ex)
            {
                HandleOleDbException(ex, SQLStr);
            }
            catch (Exception ex)
            {

                throw new MLabsException(ex.Message, LogLevelType.ERROR);
            }
            finally
            {
                try
                {
                    if (!mInTransaction)
                        connection.Close();
                }
                catch (Exception ex) { Logger.Log(ex.Source + " : " + ex.Message, LogLevelType.SQLERROR); }
            }
            return null;
        }

        public DAArrayList GetRowsAsArrayList(ResultType resType, string spCall, OleDbParameter[] paramList, bool setFieldAttributes)
        {
            OleDbConnection connection = new OleDbConnection();
            OleDbCommand command = new OleDbCommand();
            OleDbDataReader dr = null;
            DAArrayList results = new DAArrayList();
            Array oneRow;
            DataTable dt;

            DataRow lRow;

            try
            {

                if (setFieldAttributes)
                    fieldAttributes = new ArrayList();
                else
                    fieldAttributes = null;

                if (!mInTransaction)
                {
                    connection.ConnectionString = GetConnectionString();
                    connection.Open();
                }
                else
                {
                    connection = mConnection;
                }

                command.CommandText = spCall;
                command.CommandTimeout = queryTimeout;

                if (mInTransaction)
                    command.Transaction = mTransaction;

                if (paramList == null)
                {
                    command.CommandType = CommandType.Text;
                }
                else
                {
                    command.CommandType = CommandType.StoredProcedure;
                    for (int i = 0; i < paramList.Length; i++)
                    {
                        command.Parameters.Add(paramList[i]);
                    }
                }

                command.Connection = connection;

                dr = command.ExecuteReader((CommandBehavior)resType);

                if (setFieldAttributes && dr != null && dr.FieldCount > 0)
                {
                    dt = dr.GetSchemaTable();
                    if (dt.Rows != null)
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            lRow = dt.Rows[i];
                            FieldAttribute fa = new FieldAttribute();
                            fa.columnName = (string)lRow["ColumnName"];
                            fa.columnSize = (int)lRow["ColumnSize"];
                            string ct = ((System.Type)lRow["DataType"]).FullName;
                            if (ct.Equals("System.String"))
                                fa.columnType = ColumnType.String;
                            if (ct.Equals("System.Int16") || ct.Equals("System.Int32") || ct.Equals("System.Int64"))
                                fa.columnType = ColumnType.Int;
                            if (ct.Equals("System.Double"))
                                fa.columnType = ColumnType.Double;
                            if (ct.Equals("System.DateTime"))
                                fa.columnType = ColumnType.DateTime;

                            fieldAttributes.Add(fa);
                        }
                    }
                }

                results.SetFieldAttributes(fieldAttributes);

                while (dr.Read())
                {
                    oneRow = (object[])Array.CreateInstance(typeof(object), dr.FieldCount);
                    dr.GetValues((object[])oneRow);
                    results.Add(oneRow);
                }
            }
            catch (OleDbException ex)
            {
                /*if(mInTransaction)
                {
                    try
                    {
                        RollbackTrans();
                    }
                    catch (Exception ex) {}
                }*/

                OleDbErrorCollection lErrors = ex.Errors;
                string userMsg;
                userMsg = "SQL SERVER: " + spCall + ": ";
                for (int i = 0; i < lErrors.Count; i++)
                    userMsg = userMsg + lErrors[i].Message + '\t';
                if (ex.ErrorCode == 2627 || ex.ErrorCode == 547 || ex.ErrorCode == 515)
                    throw new MLabsException(userMsg, LogLevelType.SQLINTEGRITYERROR);
                else
                    throw new MLabsException(userMsg, LogLevelType.SQLERROR);
            }
            catch (Exception ex)
            {
                /*if(mInTransaction)
                {
                    try
                    {
                        RollbackTrans();
                    }
                    catch (Exception ex) {}
                }*/

                throw new MLabsException(ex.Message, LogLevelType.ERROR);
            }
            finally
            {
                try
                {
                    if (paramList != null)
                    {
                        command.Parameters.Clear();
                    }
                    if (dr != null)
                        dr.Close();
                    if (!mInTransaction)
                        connection.Close();
                }
                catch (Exception ex) { Logger.Log(ex.Source + " : " + ex.Message, LogLevelType.SQLERROR); }
            }
            return results;
        }

        public DAArrayList GetRowsAsArrayList(ResultType resType, string SQLStr, bool setFieldAttributes)
        {
            DAArrayList results = new DAArrayList();
            using (OleDbConnection connection = new OleDbConnection())
            {
                OleDbCommand command = new OleDbCommand();
                OleDbDataReader dr = null;
                Array oneRow;
                DataTable dt;

                DataRow lRow;

                try
                {

                    if (setFieldAttributes)
                        fieldAttributes = new ArrayList();
                    else
                        fieldAttributes = null;

                    //if (!mInTransaction)
                    //{
                        connection.ConnectionString = GetConnectionString();
                        connection.Open();
                    //}
                    //else
                    //{
                    //    connection = mConnection;
                    //}

                    command.CommandText = SQLStr;
                    command.CommandTimeout = queryTimeout;

                    if (mInTransaction)
                        command.Transaction = mTransaction;

                    command.CommandType = CommandType.Text;

                    command.Connection = connection;

                    dr = command.ExecuteReader((CommandBehavior)resType);

                    if (setFieldAttributes && dr != null && dr.FieldCount > 0)
                    {
                        dt = dr.GetSchemaTable();
                        if (dt.Rows != null)
                        {
                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                lRow = dt.Rows[i];
                                FieldAttribute fa = new FieldAttribute();
                                fa.columnName = (string)lRow["ColumnName"];
                                fa.columnSize = (int)lRow["ColumnSize"];
                                string ct = ((System.Type)lRow["DataType"]).FullName;
                                if (ct.Equals("System.String"))
                                    fa.columnType = ColumnType.String;
                                if (ct.Equals("System.Int16") || ct.Equals("System.Int32") || ct.Equals("System.Int64"))
                                    fa.columnType = ColumnType.Int;
                                if (ct.Equals("System.Double"))
                                    fa.columnType = ColumnType.Double;
                                if (ct.Equals("System.DateTime"))
                                    fa.columnType = ColumnType.DateTime;

                                fieldAttributes.Add(fa);
                            }
                        }
                    }

                    results.SetFieldAttributes(fieldAttributes);

                    while (dr.Read())
                    {
                        oneRow = (object[])Array.CreateInstance(typeof(object), dr.FieldCount);
                        dr.GetValues((object[])oneRow);
                        results.Add(oneRow);
                    }
                }
                catch (OleDbException ex)
                {
                    HandleOleDbException(ex, SQLStr);
                }
                catch (Exception ex)
                {

                    throw new MLabsException(ex.Message, LogLevelType.ERROR);
                }
                finally
                {
                    try
                    {
                        if (dr != null)
                            dr.Close();

                        //if (!mInTransaction)
                            connection.Close();
                    }
                    catch (Exception ex) { Logger.Log(ex.Source + " : " + ex.Message, LogLevelType.SQLERROR); }
                }
            }
            return results;
        }

        public DAArrayList GetRowsAsArrayList(ResultType resType, string spName, object[] paramsList, bool setFieldAttributes)
        {
            return GetRowsAsArrayList(resType, GetSpCall(spName, paramsList), null, setFieldAttributes);
        }

        /// <summary>
/// if Parameter value is null then function assign them to DBNull. Similary if Datetime values are 01/01/01 then these would be settled as null.
/// </summary>
/// <param name="Param"></param>
        private void SetNullables(OleDbParameter Param)
        {
            if (Param.Value == null)
            {
                Param.Value = DBNull.Value;
                return;
            } 
                
            switch (Param.DbType)
            {
                case DbType.DateTime:
                    if (Param.Value is String)
                    {
                        if (Param.Value.ToString() == "")
                        {
                            Param.Value = DBNull.Value;
                        }
                        else
                        {
                            Param.Value = Convert.ToDateTime(Param.Value);
                        }
                    }
                    else if (DateTime.Compare((DateTime)Param.Value, new DateTime(0)) == 0)
                    {
                        Param.Value = DBNull.Value;
                    }
                    break;

                case DbType.AnsiString:
                    if (Param.Value.ToString() == "")
                    {
                        Param.Value = DBNull.Value;
                    }
                    break;
                
            }
        }

        public OleDbDataReader GetRowsAsOleDbDataReader(ResultType resType, string spCall, OleDbParameter[] paramList)
        {
            OleDbConnection connection = new OleDbConnection();
            OleDbCommand command = new OleDbCommand();
            OleDbDataReader dr = null;
            DAArrayList results = new DAArrayList();

            try
            {

                if (!mInTransaction)
                {
                    connection.ConnectionString = GetConnectionString();
                    connection.Open();
                }
                else
                {
                    connection = mConnection;
                }

                command.CommandText = spCall;
                command.CommandTimeout = queryTimeout;

                if (mInTransaction)
                    command.Transaction = mTransaction;

                if (paramList == null)
                {
                    command.CommandType = CommandType.Text;
                }
                else
                {
                    command.CommandType = CommandType.StoredProcedure;
                    for (int i = 0; i < paramList.Length; i++)
                    {
                        SetNullables(paramList[i]);
                        command.Parameters.Add(paramList[i]);
                    }
                }

                command.Connection = connection;

                dr = command.ExecuteReader((CommandBehavior)resType);

            }
            catch (OleDbException ex)
            {

                OleDbErrorCollection lErrors = ex.Errors;
                string userMsg;
                userMsg = "SQL SERVER: " + spCall + ": ";
                for (int i = 0; i < lErrors.Count; i++)
                    userMsg = userMsg + lErrors[i].Message + '\t';
                if (ex.ErrorCode == 2627 || ex.ErrorCode == 547 || ex.ErrorCode == 515)
                    throw new MLabsException(userMsg, LogLevelType.SQLINTEGRITYERROR);
                else
                    throw new MLabsException(userMsg, LogLevelType.SQLERROR);
            }
            catch (Exception ex)
            {
                throw new MLabsException(ex.Message, LogLevelType.ERROR);
            }
            finally
            {
                try
                {
                    if (paramList != null)
                    {
                        command.Parameters.Clear();
                    }

                    mDr = dr;
                    mConnection = connection;
                }
                catch (Exception ex) { Logger.Log(ex.Source + " : " + ex.Message, LogLevelType.SQLERROR); }
            }
            return mDr;
        }

        public OleDbDataReader GetRowsAsOleDbDataReader(ResultType resType, string SQLStr)
        {
            OleDbConnection connection = new OleDbConnection();
            OleDbCommand command = new OleDbCommand();
            OleDbDataReader dr = null;
            DAArrayList results = new DAArrayList();

            try
            {

                //if (!mInTransaction && (mConnection == null || mConnection.State != ConnectionState.Open))
                if (!mInTransaction)
                {
                    connection.ConnectionString = GetConnectionString();
                    connection.Open();
                }
                else
                {
                    connection = mConnection;
                }

                command.CommandText = SQLStr;
                command.CommandTimeout = queryTimeout;

                if (mInTransaction)
                    command.Transaction = mTransaction;

                command.CommandType = CommandType.Text;

                command.Connection = connection;

                dr = command.ExecuteReader((CommandBehavior)resType);

            }
            catch (OleDbException ex)
            {
                HandleOleDbException(ex, SQLStr);
            }
            catch (Exception ex)
            {
                throw new MLabsException(ex.Message, LogLevelType.ERROR);
            }
            finally
            {
                try
                {
                    //if (mDr != null && !mDr.IsClosed) 
                    //    mDr.Close();

                    mDr = dr;
                    mConnection = connection;
                }
                catch (Exception ex) { Logger.Log(ex.Source + " : " + ex.Message, LogLevelType.SQLERROR); }
            }
            return mDr;
        }

        public OleDbDataReader GetRowsAsOleDbDataReader(ResultType resType, string spName, object[] paramsList)
        {
            return GetRowsAsOleDbDataReader(resType, GetSpCall(spName, paramsList), null);
        }
        
        public OleDbDataReader GetRowsAsOleDbDataReader(string spCall, OleDbParameter[] paramList)
        {
            return GetRowsAsOleDbDataReader(ResultType.Default, spCall, paramList);
        }

        public bool Update(string spCall, OleDbParameter[] paramList)
        {
            OleDbConnection connection = new OleDbConnection();
            OleDbCommand command = new OleDbCommand();
            DAArrayList results = new DAArrayList();
            bool execStatus = false;

            try
            {

                if (!mInTransaction)
                {
                    connection.ConnectionString = GetConnectionString();
                    connection.Open();
                }
                else
                {
                    connection = mConnection;
                }

                command.CommandText = spCall;
                command.CommandTimeout = queryTimeout;

                if (mInTransaction)
                    command.Transaction = mTransaction;

                if (paramList == null)
                {
                    command.CommandType = CommandType.Text;
                }
                else
                {
                    command.CommandType = CommandType.StoredProcedure;
                    for (int i = 0; i < paramList.Length; i++)
                    { 
                        SetNullables(paramList[i]);
                        command.Parameters.Add(paramList[i]);
                    }
                }

                command.Connection = connection;
                OleDbDataReader dbResult;
                dbResult = command.ExecuteReader();
                if (dbResult.Read())
                {
                    if (dbResult.FieldCount > 0)
                        db_rtn_code = (Int32)dbResult.GetInt32(0);
                    else
                        db_rtn_code = 0;

                }
                execStatus = true;
            }
            catch (OleDbException ex)
            {

                OleDbErrorCollection lErrors = ex.Errors;
                string userMsg;
                userMsg = "SQL SERVER: " + spCall + ": ";
                for (int i = 0; i < lErrors.Count; i++)
                    userMsg = userMsg + lErrors[i].Message + '\t';
                if (ex.ErrorCode == 2627 || ex.ErrorCode == 547 || ex.ErrorCode == 515)
                    throw new MLabsException(userMsg, LogLevelType.SQLINTEGRITYERROR);
                else
                    throw new MLabsException(userMsg, LogLevelType.SQLERROR);

            }
            catch (Exception ex)
            {
                throw new MLabsException(ex.Message, LogLevelType.ERROR);

            }
            finally
            {
                try
                {
                    if (paramList != null)
                    {
                        command.Parameters.Clear();
                    }
                    if (!mInTransaction)
                        connection.Close();
                }
                catch (Exception ex) { Logger.Log(ex.Source + " : " + ex.Message, LogLevelType.SQLERROR); }

            }
            return execStatus;
        }

        public int Update(string SQLStr)
        {
            OleDbConnection connection = new OleDbConnection();
            OleDbCommand command = new OleDbCommand();
            int dbResult;

            try
            {

                if (!mInTransaction)
                {
                    connection.ConnectionString = GetConnectionString();
                    connection.Open();
                }
                else
                {
                    connection = mConnection;
                }

                command.CommandText = SQLStr;
                command.CommandTimeout = queryTimeout;

                if (mInTransaction)
                    command.Transaction = mTransaction;

                command.CommandType = CommandType.Text;

                command.Connection = connection;

                dbResult = command.ExecuteNonQuery();
                
            }
            catch (OleDbException ex)
            {

                OleDbErrorCollection lErrors = ex.Errors;
                string userMsg;
                userMsg = "SQL SERVER: " + SQLStr + ": ";
                for (int i = 0; i < lErrors.Count; i++)
                    userMsg = userMsg + lErrors[i].Message + '\t';
                if (ex.ErrorCode == 2627 || ex.ErrorCode == 547 || ex.ErrorCode == 515)
                    throw new MLabsException(userMsg, LogLevelType.SQLINTEGRITYERROR);
                else
                    throw new MLabsException(userMsg, LogLevelType.SQLERROR);

            }
            catch (Exception ex)
            {
                throw new MLabsException(ex.Message, LogLevelType.ERROR);
            }
            finally
            {
                try
                {
                    if (!mInTransaction)
                        connection.Close();
                }
                catch (Exception ex) { Logger.Log(ex.Source + " : " + ex.Message, LogLevelType.SQLERROR); }

            }
            return dbResult;
        }

        public bool Update(string spName, object[] paramsList)
        {
            return Update(GetSpCall(spName, paramsList), null);
        }

        public void BeginTrans()
        {
            try
            {
                Connect();
                mTransaction = mConnection.BeginTransaction();
                mInTransaction = true;
            }
            catch (OleDbException ex)
            {
                OleDbErrorCollection lErrors = ex.Errors;
                string userMsg;
                userMsg = "SQL SERVER: ";
                for (int i = 0; i < lErrors.Count; i++)
                    userMsg = userMsg + lErrors[i].Message + '\t';
                throw new MLabsException(userMsg, LogLevelType.SQLERROR);
            }
            catch (Exception ex)
            {
                throw new MLabsException(ex.Message, LogLevelType.ERROR);
            }
        }

        public void RollbackTrans()
        {
            try
            {
                if (mInTransaction)
                    mTransaction.Rollback();
                else
                    throw new MLabsException("No transaction exist to roll back", LogLevelType.ERROR);
            }
            catch (OleDbException ex)
            {
                OleDbErrorCollection lErrors = ex.Errors;
                string userMsg;
                userMsg = "SQL SERVER: ";
                for (int i = 0; i < lErrors.Count; i++)
                    userMsg = userMsg + lErrors[i].Message + '\t';
                throw new MLabsException(userMsg, LogLevelType.SQLERROR);
            }
            catch (Exception ex)
            {
                throw new MLabsException(ex.Message, LogLevelType.ERROR);
            }
            finally
            {
                mInTransaction = false;
                try
                {
                    mConnection.Close();
                }
                catch (Exception ex) { Logger.Log(ex.Source + " : " + ex.Message, LogLevelType.SQLERROR); }
            }
        }


        public void CommitTrans()
        {
            try
            {
                if (mInTransaction)
                    mTransaction.Commit();
                else
                    throw new MLabsException("No transaction exist to commit", LogLevelType.ERROR);

            }
            catch (OleDbException ex)
            {
                OleDbErrorCollection lErrors = ex.Errors;
                string userMsg;
                userMsg = "SQL SERVER: ";
                for (int i = 0; i < lErrors.Count; i++)
                    userMsg = userMsg + lErrors[i].Message + '\t';
                throw new MLabsException(userMsg, LogLevelType.SQLERROR);
            }
            catch (Exception ex)
            {
                throw new MLabsException(ex.Message, LogLevelType.ERROR);
            }
            finally
            {
                mInTransaction = false;
                try
                {
                    mConnection.Close();
                }
                catch (Exception ex) { Logger.Log(ex.Source + " : " + ex.Message, LogLevelType.SQLERROR); }
            }
        }

        public void CloseDataReader()
        {
            try
            {
                mDr.Close();
                if (!mInTransaction)
                    mConnection.Close();
            }
            catch (Exception ex) { Logger.Log(ex.Source + " : " + ex.Message, LogLevelType.SQLERROR); }
        }

        private string GetSpCall(string spName, object[] paramsList)
        {
            string spCall = spName;
            string temp = "";
            Byte[] sbi;
            string sbiTemp;
            string h;


            for (int i = 0; i < paramsList.Length; i++)
            {
                if (paramsList[i] != null)
                {
                    string pFullName = paramsList[i].GetType().FullName;
                    if (pFullName.Equals("System.Byte[]"))
                    {
                        sbi = (Byte[])paramsList[i];
                        sbiTemp = "0x";
                        for (int j = 0; j < sbi.Length; j++)
                        {
                            char ch = (char)sbi[j];
                            h = Uri.HexEscape(ch);
                            h = h.Substring(1);
                            if (h.Length == 1)
                                h = "0" + h;
                            sbiTemp = sbiTemp + h;
                        }
                        temp = temp + sbiTemp + ", ";
                    }
                    else
                    {
                        string sP = paramsList[i].ToString();
                        if (sP.Equals("<<null>>") || sP.Equals("<<NULL>>"))
                            temp = temp + "NULL, ";
                        else
                        {
                            if (pFullName.Equals("System.Int16") ||
                                pFullName.Equals("System.Int32") ||
                                pFullName.Equals("System.Int64") ||
                                pFullName.Equals("System.Integer") ||
                                pFullName.Equals("System.Long") ||
                                pFullName.Equals("System.Double") ||
                                pFullName.Equals("System.Boolean") ||
                                pFullName.Equals("System.Byte") ||
                                pFullName.Equals("System.Decimal") ||
                                pFullName.Equals("System.Short") ||
                                pFullName.Equals("System.Single"))
                                temp = temp + paramsList[i].ToString() + ", ";
                            if (pFullName.Equals("System.Char") ||
                                pFullName.Equals("System.String") ||
                                pFullName.Equals("System.Date"))
                                temp = temp + "'" + (paramsList[i].ToString()).Replace("'", "''") + "', ";
                        }
                    }
                }
            }

            if (temp.Length > 0)
            {
                temp = temp.Substring(0, temp.Length - 2);
                spCall = spCall + " " + temp;
            }

            return spCall;
        }


        public DataSet GetRowsAsDataSet(string spCall, OleDbParameter[] paramList)
        {
            OleDbConnection connection = new OleDbConnection();
            OleDbCommand command = new OleDbCommand();
            DataSet ds = new DataSet();
            OleDbDataAdapter da = null;

            try
            {

                if (!mInTransaction)
                {
                    connection.ConnectionString = GetConnectionString();
                    connection.Open();
                }
                else
                {
                    connection = mConnection;
                }

                command.CommandText = spCall;
                command.CommandTimeout = queryTimeout;

                if (mInTransaction)
                    command.Transaction = mTransaction;

                if (paramList == null)
                {
                    command.CommandType = CommandType.Text;
                }
                else
                {
                    command.CommandType = CommandType.StoredProcedure;
                    for (int i = 0; i < paramList.Length; i++)
                    {
                        command.Parameters.Add(paramList[i]);
                    }
                }

                command.Connection = connection;

                da = new OleDbDataAdapter(command);
                da.Fill(ds);

            }
            catch (OleDbException ex)
            {
                OleDbErrorCollection lErrors = ex.Errors;
                string userMsg;
                userMsg = "SQL SERVER: " + spCall + ": ";
                for (int i = 0; i < lErrors.Count; i++)
                    userMsg = userMsg + lErrors[i].Message + '\t';
                if (ex.ErrorCode == 2627 || ex.ErrorCode == 547 || ex.ErrorCode == 515)
                    throw new MLabsException(userMsg, LogLevelType.SQLINTEGRITYERROR);
                else
                    throw new MLabsException(userMsg, LogLevelType.SQLERROR);
            }
            catch (Exception ex)
            {
                throw new MLabsException(ex.Message, LogLevelType.ERROR);
            }
            finally
            {
                try
                {
                    if (paramList != null)
                    {
                        command.Parameters.Clear();
                    }

                    mDs = ds;
                    mConnection = connection;
                }
                catch (Exception ex) { Logger.Log(ex.Source + " : " + ex.Message, LogLevelType.SQLERROR); }
            }
            return ds;
        }

        public DataSet GetRowsAsDataSet(string SQLStr)
        {
            OleDbConnection connection = new OleDbConnection();
            OleDbCommand command = new OleDbCommand();
            DataSet ds = new DataSet();
            OleDbDataAdapter da = null;

            try
            {

                if (!mInTransaction)
                {
                    connection.ConnectionString = GetConnectionString();
                    connection.Open();
                }
                else
                {
                    connection = mConnection;
                }

                command.CommandText = SQLStr;
                command.CommandTimeout = queryTimeout;

                if (mInTransaction)
                    command.Transaction = mTransaction;

                command.CommandType = CommandType.Text;

                command.Connection = connection;

                da = new OleDbDataAdapter(command);
                da.Fill(ds);

            }
            catch (OleDbException ex)
            {
                HandleOleDbException(ex, SQLStr);
            }
            catch (Exception ex)
            {
                throw new MLabsException(ex.Message, LogLevelType.ERROR);
            }
            finally
            {
                try
                {
                    mDs = ds;
                    mConnection = connection;
                }
                catch (Exception ex) { Logger.Log(ex.Source + " : " + ex.Message, LogLevelType.SQLERROR); }
            }
            return ds;
        }

        public void GetRowsAsDataSet(string SQLStr, ref DataSet prmDs)
        {
            OleDbConnection connection = new OleDbConnection();
            OleDbCommand command = new OleDbCommand();
            OleDbDataAdapter da = null;

            try
            {

                if (!mInTransaction)
                {
                    connection.ConnectionString = GetConnectionString();
                    connection.Open();
                }
                else
                {
                    connection = mConnection;
                }

                command.CommandText = SQLStr;
                command.CommandTimeout = queryTimeout;

                if (mInTransaction)
                    command.Transaction = mTransaction;

                command.CommandType = CommandType.Text;

                command.Connection = connection;

                if (prmDs == null) prmDs = new DataSet();
                da = new OleDbDataAdapter(command);
                if (prmDs.Tables.Count>0) prmDs.Tables[0].Clear();
                da.Fill(prmDs);

            }
            catch (OleDbException ex)
            {
                HandleOleDbException(ex, SQLStr);
            }
            catch (Exception ex)
            {
                throw new MLabsException(ex.Message, LogLevelType.ERROR);
            }
            finally
            {
                try
                {
                    mDs = prmDs;
                    mConnection = connection;
                }
                catch (Exception ex) { Logger.Log(ex.Source + " : " + ex.Message, LogLevelType.SQLERROR); }
            }
            //return prmDs;
            return;
        }


        /// <summary>
        /// This function take care of multiple result sets and return all in on row. Although it seems it is not
        /// useful but in case of multiple result sets are returned due to the nature of
        /// stored procedures used in those. Only the last result sets is to be checked as it has ERROR_CODE field.
        /// It is not posible to have other command behaviors than Default so that is omitted.
        /// It is also no possible to build field attribute info as different result sets can have different Schemas
        /// </summary>
        /// <param name="spCall"> Name of the Stored procedure. Anything else wil through an exception</param>
        /// <param name="paramList">Parameter List of the procedure</param>
        /// <returns></returns>
        public DAArrayList GetBatchAsArrayList(string spCall, OleDbParameter[] paramList)
        {
            OleDbConnection connection = new OleDbConnection();
            OleDbCommand command = new OleDbCommand();
            OleDbDataReader dr = null;
            DAArrayList results = new DAArrayList();
            Array oneRow;

            try
            {


                if (!mInTransaction)
                {
                    connection.ConnectionString = GetConnectionString();
                    connection.Open();
                }
                else
                {
                    connection = mConnection;
                }

                command.CommandText = spCall;
                command.CommandTimeout = queryTimeout;

                if (mInTransaction)
                    command.Transaction = mTransaction;

                command.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < paramList.Length; i++)
                {
                    command.Parameters.Add(paramList[i]);
                }

                command.Connection = connection;

                dr = command.ExecuteReader();


                do
                {
                    while (dr.Read())
                    {
                        oneRow = (object[])Array.CreateInstance(typeof(object), dr.FieldCount);
                        dr.GetValues((object[])oneRow);
                        results.Add(oneRow);
                    }
                } while (dr.NextResult());
            }
            catch (OleDbException ex)
            {
                OleDbErrorCollection lErrors = ex.Errors;
                string userMsg;
                userMsg = "SQL SERVER: " + spCall + ": ";
                for (int i = 0; i < lErrors.Count; i++)
                    userMsg = userMsg + lErrors[i].Message + '\t';
                if (ex.ErrorCode == 2627 || ex.ErrorCode == 547 || ex.ErrorCode == 515)
                    throw new MLabsException(userMsg, LogLevelType.SQLINTEGRITYERROR);
                else
                    throw new MLabsException(userMsg, LogLevelType.SQLERROR);
            }
            catch (Exception ex)
            {
                throw new MLabsException(ex.Message, LogLevelType.ERROR);
            }
            finally
            {
                try
                {
                    if (paramList != null)
                    {
                        command.Parameters.Clear();
                    }
                    if (dr != null)
                        dr.Close();
                    if (!mInTransaction)
                        connection.Close();
                }
                catch (Exception ex) { Logger.Log(ex.Source + " : " + ex.Message, LogLevelType.SQLERROR); }
            }
            return results;
        }

        /// <summary>
        /// Used in 7.3
        /// </summary>
        /// <param name="resType"></param>
        /// <param name="spCall"></param>
        /// <param name="paramList"></param>
        /// <param name="setFieldAttributes"></param>
        /// <param name="paramname"></param>
        /// <returns></returns>
        public string getoutPutparam(ResultType resType, string spCall, OleDbParameter[] paramList, bool setFieldAttributes, string paramname)
        {
            string val = "";
            OleDbConnection connection = new OleDbConnection();
            OleDbCommand command = new OleDbCommand();
            OleDbDataReader dr = null;

            try
            {

                if (setFieldAttributes)
                    fieldAttributes = new ArrayList();
                else
                    fieldAttributes = null;

                if (!mInTransaction)
                {
                    connection.ConnectionString = GetConnectionString();
                    connection.Open();
                }
                else
                {
                    connection = mConnection;
                }

                command.CommandText = spCall;
                command.CommandTimeout = int.Parse(ConfigurationManager.AppSettings.Get("QueryTimeout_DB"));

                if (mInTransaction)
                    command.Transaction = mTransaction;

                if (paramList == null)
                {
                    command.CommandType = CommandType.Text;
                }
                else
                {
                    command.CommandType = CommandType.StoredProcedure;
                    for (int i = 0; i < paramList.Length; i++)
                    {
                        command.Parameters.Add(paramList[i]);
                    }
                }

                command.Connection = connection;
                dr = command.ExecuteReader((CommandBehavior)resType);
                val = command.Parameters[paramname].Value.ToString();

            }
            catch (OleDbException ex)
            {

                OleDbErrorCollection lErrors = ex.Errors;
                string userMsg;
                userMsg = "SQL SERVER: " + spCall + ": ";
                for (int i = 0; i < lErrors.Count; i++)
                    userMsg = userMsg + lErrors[i].Message + '\t';
                if (ex.ErrorCode == 2627 || ex.ErrorCode == 547 || ex.ErrorCode == 515)
                    throw new MLabsException(userMsg, LogLevelType.SQLINTEGRITYERROR);
                else
                    throw new MLabsException(userMsg, LogLevelType.SQLERROR);
            }
            catch (Exception ex)
            {

                throw new MLabsException(ex.Message, LogLevelType.ERROR);
            }
            finally
            {
                try
                {
                    if (paramList != null)
                    {
                        command.Parameters.Clear();
                    }
                    if (dr != null)
                        dr.Close();
                    if (!mInTransaction)
                        connection.Close();
                }
                catch (Exception ex) { Logger.Log(ex.Source + " : " + ex.Message, LogLevelType.SQLERROR); }
            }

            return val;
        }


        private void HandleOleDbException(OleDbException ex, string SQLStr)
        {
            switch (ex.ErrorCode)
            {
                case (2):       //DataAccessErrorType.NetworkAddressNotFound;
                case (53):      //DataAccessErrorType.InvalidDatabase;
                case (4060):    //DataAccessErrorType.LoginFailed;
                case (18452):
                case (18456):   ////DataAccessErrorType.ConnectionRefused;
                case (10054):
                case (547):     //DataAccessErrorType.SqlError;
                case (2627):
                case (2601):
                    if (AppMode == AppModes.Debug)
                    {
                        throw new MLabsException(ex.ErrorCode, "Enable to connect to given database server. Please verify name of database server in configuration file and confirm if server is running\n\n" + ex.Message, LogLevelType.ERROR);
                    }
                    else
                    {
                        throw new MLabsException(ex.ErrorCode, "Enable to connect to given database server. Please verify name of database server in configuration file and confirm if server is running", LogLevelType.ERROR);
                    }
                //break;
                default:
                    OleDbErrorCollection lErrors = ex.Errors;
                    string userMsg;
                    userMsg = "SQL SERVER: " + SQLStr + ": ";
                    if (AppMode == AppModes.Debug)
                    {
                        for (int i = 0; i < lErrors.Count; i++)
                            userMsg = userMsg + lErrors[i].Message + '\t';
                        if (ex.ErrorCode == 2627 || ex.ErrorCode == 547 || ex.ErrorCode == 515)
                            throw new MLabsException(ex.ErrorCode, userMsg, LogLevelType.SQLINTEGRITYERROR);
                        else
                            throw new MLabsException(ex.ErrorCode, userMsg, LogLevelType.SQLERROR);
                    }
                    else
                    {
                        throw new MLabsException(ex.ErrorCode, "There was an internal database script error. Please contact technical support team for more assistance", LogLevelType.SQLERROR);
                    }
            }


        }



        public static int GetFormsQueryTimeOut()
        {
            if (ConfigurationManager.AppSettings["QueryTimeout_Forms"] != null)
                return Convert.ToInt32(ConfigurationManager.AppSettings["QueryTimeout_Forms"]);
            else
                return 120;
        }



    }


}
